package com.xiaozhi.utils;

import com.xiaozhi.entity.Question;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class excel {

    /**
     * 将 Excel 中的数据转换为相关的实体
     * @param file 源Excel
     * @return 转换后的数据
     */
    public static Collection<Question> convertExcel(MultipartFile file) throws Exception {
        Collection<Question> list = new ArrayList<>();
        //1.得到上传的表
        Workbook workbook2 = WorkbookFactory.create(file.getInputStream());

        //2、获取test工作表
        Sheet sheet1 = workbook2.getSheet("Sheet1");
        //获取表的总行数
        int num = sheet1.getLastRowNum();
        System.out.println(num);
        //总列数
        int col = sheet1.getRow(0).getLastCellNum();

        //遍历excel每一行
        for(int j = 0; j <= num; j++) {
            Row row = sheet1.getRow(j);
            //判断该行是不是Excel输入用例，如果是则跳出该次循环
            Cell cell0 = row.getCell(0);
            cell0.setCellType(CellType.STRING);
            if(cell0.getStringCellValue().equals("0") || cell0.getStringCellValue().equals("编号")){
                continue;
            }

            //这里new 一个对象，用来装填从页面上传的Excel数据，字段根据上传的excel决定
            Question question = new Question();

            //如果单元格中有数字或者其他格式的数据，则调用setCellType()转换为string类型
            //处理学科、题目、类型、答案
            Cell cell1 = row.getCell(1);
            Cell cell2 = row.getCell(2);
            Cell cell3 = row.getCell(3);
            Cell cell4 = row.getCell(4);
            cell4.setCellType(CellType.STRING);
            //处理选项
            Cell cell5 = row.getCell(5);
            Cell cell6 = row.getCell(6);
            Cell cell7 = row.getCell(7);
            Cell cell8 = row.getCell(8);

            question.setSubject(cell1.getStringCellValue());
            question.setQuestionName(cell2.getStringCellValue());
            question.setType(cell3.getStringCellValue());
            question.setAnswer(cell4.getStringCellValue());
            String options=null;
            //如果选项为空的话，不在进行拼接字符串, 拼接后的字符串：A、1.B、2.C、3.D、4
            if (cell5==null){}
            else
                options= cell5.getStringCellValue() + "^~." +cell6.getStringCellValue() + "^~." +
                        cell7.getStringCellValue() + "^~." +cell8.getStringCellValue();
            question.setOptions(options);

            list.add(question);
        }
        return list;
    }
}
